

clear 

import excel using "OrigData/PV installation price.xlsx", sheet("Sheet2") firstrow


destring PV*, replace


keep if state ==6

gen quarter = 1 if month<=3
replace quarter = 2 if quarter==. & month <=6
replace quarter = 3 if quarter==. & month <=9
replace quarter = 4 if quarter==. & month <=12

gen contract_quarter = yq(year,quarter)

format contract_quarter %tq

collapse (mean) PV* , by(contract_quarter)

save Data/PVinstallationprices.dta, replace





merge m:1 contract_quarter using Data/BNEF_module_quarter.dta
	
	
	drop _merge

	
** predict missing installation prices

reg PVPrice3kW moduleprice	

predict PVpricepredicted
	
save Data/PVprices_predicted.dta, replace
	
	

clear
import excel using OrigData/NPV.xlsx, cellrange(A3:B8)

xpose, clear

rename v1 electricity_savings
rename v2 export_income
rename v3 SRES
rename v4 system_cost
rename v5 system_maintenace
rename v6 net_benefit



drop if _n==1

gen year = 2015

gen quarter = 1

gen contract_quarter = yq(year,quarter)

format contract_quarter %tq


gen SRES_price = SRES/(1.185*15*3)

gen kWh = export_income/(0.0625*13.6)


merge 1:m contract_quarter using Data/PVprices_predicted.dta

sort contract_quarter


** calculate SRES value

 gen SRES_multiplier  = 5 if contract_quarter <= tq(2011q2) & contract_quarter > tq(2009q2)
 replace SRES_multiplier = 3 if contract_quarter <= tq(2012q2) & SRES_multiplier==.
 replace SRES_multiplier = 2 if contract_quarter <= tq(2012q4)  & SRES_mu==.
	replace SRES_multiplier = 1 if contract_quarter >= tq(2013q1) 

 
 gen SRES_value = 35*1.185*15*1.5 + (35*1.185*15*1.5*SRES_multiplier) // multipler is first 1.5kW

 
 
 ** calculate FIT value 
 summ kWh 
 local mean_kWh = r(mean)
 replace kWh = `mean_kWh'
 
 
gen currentFIT = 0.0625 if contract_quarter >= tq(2014q1)
replace currentFIT = 0.08 if contract_quarter >= tq(2013q1) & contract_quarter <= tq(2013q4)
replace currentFIT = 0.25 if contract_quarter >= tq(2011q4) & contract_quarter <= tq(2012q4)
replace currentFIT = 0.6 if contract_quarter > = tq(2009q4) & contract_quarter <= tq(2011q3)
replace currentFIT = 0.22 if contract_quarter <  tq(2009q4) & contract_quarter >= tq(2008q1)

 
 
 gen timeleftFIT = round((tq(2024q4) -  contract_quarter)/4) if contract_quarter > = tq(2009q4) & contract_quarter <= tq(2011q3)
 replace timeleftFIT =  round((tq(2017q1) -  contract_quarter)/4) if contract_quarter <  tq(2009q4) | currentFIT ==0.25
 replace timeleftFIT = 0 if timeleftFIT ==.
 
 
 gen baseFITrev = kWh * currentFIT * 10.9 if timeleftFIT == 15
 replace baseFITrev = kWh * currentFIT * 9.9 if timeleftFIT==14
 replace baseFITrev = kWh * currentFIT * 8.9 if timeleftFIT==13
 replace baseFITrev = kWh * currentFIT * 8 if timeleftFIT==12
 replace baseFITrev = kWh *  currentFIT * 7.2 if timeleftFIT==11
 replace baseFITrev = kWh *  currentFIT * 6.4 if timeleftFIT==10
 replace baseFITrev = kWh * currentFIT * 5.6 if timeleftFIT==9
 replace baseFITrev = kWh *  currentFIT * 4.8 if timeleftFIT==8
 replace baseFITrev = kWh * currentFIT * 4.1 if timeleftFIT==7
 replace baseFITrev = kWh * currentFIT * 3.4 if timeleftFIT==6
 replace baseFITrev = kWh * currentFIT * 2.8 if timeleftFIT==5
 replace baseFITrev = kWh * currentFIT * 2.2 if timeleftFIT==4
 replace baseFITrev = kWh *  currentFIT * 1.6 if timeleftFIT==3
 replace baseFITrev = kWh *  currentFIT * 1 if timeleftFIT==2
 replace baseFITrev = kWh *  currentFIT * 0.5 if timeleftFIT==1
 replace baseFITrev= 0 if baseFITrev==.
 
 
 
 
 
 gen additionalFIT = 15-timeleftFIT
 
 gen minFITrev =  kWh *  0.0625 * 13.6 if additionalFIT==15
 replace  minFITrev = kWh *  0.0625 * 9.7 if additionalFIT==11
 replace  minFITrev = kWh *  0.0625 * 8.7 if additionalFIT==10
 replace  minFITrev = kWh *  0.0625 * 6 if additionalFIT==7
 replace  minFITrev = kWh *  0.0625 * 1.6 if additionalFIT==2
 replace  minFITrev = kWh *  0.0625 * 0.8 if additionalFIT==1
 replace  minFITrev = 0 if minFITrev==.
 

gen FIT_income = baseFITrev + minFITrev










summ electricity_savings
local billsave = r(mean)

gen discount_billreduction = `billsave'

summ system_main
local main = r(mean)

gen discounted_maintenance = `main'
 
 gen NPV  = FIT_income + discount_billreduction + SRES_value - PVpricepred + discounted_maintenance
 
 sort contract_quarter
 
 label variable SRES_value "Lump sum subsidy"
 label variable FIT_income "Feed-in revenue"
 label variable PVpricepred "Installation cost"
 
twoway (connected NPV contract_quarter if contract_quarter < tq(2016q1))  ///
(line SRES_value contract_quarter if contract_quarter < tq(2016q1), lpattern(dash)  lcolor(gs5))  ///
(line FIT_income contract_quarter if contract_quarter < tq(2016q1), lpattern(dot) lcolor(gs0))  ///
(line PVprice contract_quarter if contract_quarter < tq(2016q1), lpattern(dash_dot) lcolor(gs5)) 


gen subsidy = FIT_income + SRES_value

label variable subsidy "Total subsidy"
label variable contract_quarter "Quarter"


twoway  ///
(bar NPV contract_quarter if contract_quarter < tq(2016q1), color(gs8) fintensity(inten50) yaxis(2) )  ///
(connected PVprice contract_quarter if contract_quarter < tq(2016q1),  lpattern(dash_dot) msymbol(+) mcolor(gs0) lcolor(gs0) lwidth(vthin)) ///
(line subsidy contract_quarter if contract_quarter < tq(2016q1) ,   lwidth(thin) lcolor(gs2) ),  ///
legend(bmargin(zero) region(lstyle(none)) rows(1) size(large)) ylabel(#5, labsize(large)) ///
 xlabel(#4, labsize(large)) ylabel(#4, axis(2) labsize(large)) ytitle("NPV ($)", axis(2) size(large)) ytitle("Subsidies/Cost ($)", axis(1) size(large))

graph export Analysis/NPV.png, replace

graph export Analysis/NPV.eps, replace















